﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LiteExpress.Core.DbClient;
using Dapper;

namespace LiteExpress.Demo.Data.Adapters
{
    public class UserAdapter : LiteExpress.Demo.Data.IUserAdapter
    {
        public string DBName = "CorePassport";

        #region transactions
        public void TestTrans()
        {
            DbFactory.GetTransaction(DBName, (trans) =>
            {
                var conn = trans.Connection;
                conn.Query<UserEntity>("SELECT TOP 10 * FROM Users", null, trans);
                conn.Execute("INSERT INTO Users (UserName) VALUES (@UserName);", new { UserName = "fdasf"}, trans);
                return true;
            });

            return;
        }

        #endregion

        #region test case
        public IEnumerable<UserEntity> TestSql()
        {
            var userlist2 = DbFactory.GetClient(DBName, (conn) =>
            {
                return conn.Query<UserEntity>("SELECT TOP 10 * FROM Users");
            });

            return userlist2;            
        }

        public IEnumerable<UserEntity> TestSqlMap()
        {
            var result = DbFactory.GetClient(DBName, "User.SelectTest", (conn, sql) =>
            {
                return conn.Query<UserEntity>(sql);
            });
            return result;
        }
        #endregion

        #region basic
        public UserEntity Select(long userID)
        {
            //SELECT * FROM Users WHERE UserID=@UserID
            var result = DbFactory.GetClient(DBName, (conn) =>
            {
                return conn.Query<UserEntity>("SELECT * FROM Users WHERE UserID=@UserID", new { UserID = userID });
            }).FirstOrDefault();

            return result;
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="page">页码，从1开始</param>
        /// <param name="size">分页大小</param>
        public IEnumerable<UserEntity> SelectList(int page, int size)
        {
            var result = DbFactory.GetClient(DBName, (conn) =>
            {
                string sql = conn.GeneratePageSql("Users", "UserID,UserName,Email,RealName,NickName,CreateTime,CreateIP,UpdateTime,ActivateTime,LastLoginTime,LastLoginIP,LoginNumber,IsActived,IsLocked,IsDeleted,UserLanguage,FromSystem,FromSysID,UserHostID,OrgHostID", null, "UserID", "UserID DESC");
                return conn.Query<UserEntity>(string.Format(sql, page, size));
            });

            return result;
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="page">页码，从1开始</param>
        /// <param name="size">分页大小</param>
        /// <param name="orderBy">排序规则，譬如："UserID DESC"</param>
        /// <param name="wheresql">自定义查询条件</param>
        /// <param name="param">如果查询条件中有参数，可在这里传递，譬如：new { UserID = 543001}</param>
        public IEnumerable<UserEntity> SelectList(int page, int size, string orderBy, string wheresql, object param = null)
        {
            var result = DbFactory.GetClient(DBName, (conn) =>
            {
                string sql = conn.GeneratePageSql("Users", "UserID,UserName,Email,RealName,NickName,CreateTime,CreateIP,UpdateTime,ActivateTime,LastLoginTime,LastLoginIP,LoginNumber,IsActived,IsLocked,IsDeleted,UserLanguage,FromSystem,FromSysID,UserHostID,OrgHostID", wheresql, "UserID", orderBy);
                return conn.Query<UserEntity>(string.Format(sql, page, size), param);
            });

            return result;
        }

        public long Insert(UserEntity user)
        {
            //sql = "INSERT INTO Users (...) VALUES (@...); SELECT @@IDENTITY AS Id;"
            var result = DbFactory.GetClient(DBName, (conn) =>
            {
                return conn.Query<long>("INSERT INTO Users (UserID, UserName) VALUES (@UserID,@UserName);SELECT @@IDENTITY;", user).FirstOrDefault();
            });

            return result;
        }

        public long Update(UserEntity user)
        {
            //UPDATE Users SET.... WHERE UserID=@UserID;
            var result = DbFactory.GetClient(DBName, (conn) =>
            {
                return conn.Execute("UPDATE Users SET UserName=@UserName WHERE UserID=@UserID", user);
            });

            if (result > 0)
            {
                return user.UserID;
            }
            return 0;
        }

        public long Save(UserEntity user)
        {
            if (user.UserID > 0)
            {
                return Update(user);
            }
            else
            {
                return Insert(user);
            }
        }

        public int Delete(long userID)
        {
            //DELETE FROM Users WHERE UserID=@UserID;
            var result = DbFactory.GetClient(DBName, (conn) =>
            {
                return conn.Execute("DELETE FROM Users WHERE UserID=@UserID", userID);
            });

            return result;
        }
        #endregion
    }
}
